package bdddaosimpl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import bdd.beans.*;
import bdddaos.*;

public class DAOFormationImpl implements DAOFormation {
	private DBLink dblink;

	public DAOFormationImpl() {		
		super();
		this.dblink = new DBLink(); 
		this.dblink.connexion();
	}

	public Formation save(Formation o) {
		try {
			this.dblink.connexion();
			
			DAOContratquadriennalImpl daoContrat = new DAOContratquadriennalImpl();
			o.setContratquadriennal(daoContrat.save(o.getContratquadriennal()));
			
			String r = "INSERT INTO FORMATION (LIBELLE, NIVEAU, PARCOURS, TYPEFORMATION, NUMCONTRAT)" +
			" VALUES ("+o.getLibelle()+ ", "+ o.getNiveau() +", "+o.getParcours() + ", " + o.getTypeformation()+", " + o.getContratquadriennal().getNumcontrat()+")";
			this.dblink.setPst(this.dblink.getConnection().prepareStatement(r));
			this.dblink.getPst().executeQuery();
			DAOOutils outil = new DAOOutils();
			o.setNumformation(outil.findMaxId("FORMATION", "NUMFORMATION"));
		}
		catch(SQLException se) {
			System.out.println("Erreur de requ�te : " + se);
		}
		finally{
			this.dblink.deconnexion();
		}
		return o;
	}

	public Formation find(Integer id) {
		Formation retour = new Formation();
		retour.setNumformation(id);
		try {
			this.dblink.connexion();
			String r = "SELECT * FROM FORMATION WHERE NUMFORMATION = " + id;
			this.dblink.setPst(this.dblink.getConnection().prepareStatement(r));
			ResultSet rs = this.dblink.getPst().executeQuery();
			while(rs.next()) {
				retour.setLibelle(rs.getString("LIBELLE"));
				retour.setNiveau(rs.getString("NIVEAU"));
				retour.setNumformation(rs.getInt("NUMFORMATION"));
				retour.setParcours(rs.getString("PARCOURS"));
				retour.setTypeformation(rs.getString("TYPEFORMATION"));
				retour.setContratquadriennal(new DAOContratquadriennalImpl().find(rs.getInt("NUMCONTRAT")));
			}		
		}
		catch(SQLException se) {
			System.out.println("Erreur de requ�te" + se);
		}
		finally{
			this.dblink.deconnexion();
		}
		return retour;
	}

	public List<Formation> findAll() {
		List<Formation> Lstretour = new ArrayList<Formation>();
		try {
			this.dblink.connexion();
			this.dblink.setPst(this.dblink.getConnection().prepareStatement("SELECT * FROM FORMATION"));
			ResultSet rs = this.dblink.getPst().executeQuery();
			while(rs.next()) {
				Formation retour = new Formation();
				retour.setLibelle(rs.getString("LIBELLE"));
				retour.setNiveau(rs.getString("NIVEAU"));
				retour.setNumformation(rs.getInt("NUMFORMATION"));
				retour.setParcours(rs.getString("PARCOURS"));
				retour.setTypeformation(rs.getString("TYPEFORMATION"));
				retour.setContratquadriennal(new DAOContratquadriennalImpl().find(rs.getInt("NUMCONTRAT")));
				
				Lstretour.add(retour);
			}		
		}
		catch(SQLException se) {
			System.out.println("Erreur de requ�te" + se);
		}
		finally{
			this.dblink.deconnexion();
		}
		return Lstretour;
	}

	public void update(Formation o) {
		try {
			this.dblink.connexion();
			String r = "UPDATE FORMATION SET LIBELLE='"+o.getLibelle()+ "', NIVEAU='"+ o.getNiveau() +"', TYPEFORMATION='"+o.getTypeformation() + "', PARCOURS=" + o.getParcours()+", NUMCONTRAT=" + o.getContratquadriennal().getNumcontrat()+" WHERE NUMFORMATION="+o.getNumformation();
			
			this.dblink.setPst(this.dblink.getConnection().prepareStatement(r));
			this.dblink.getPst().executeQuery();
		}
		catch(SQLException se) {
			System.out.println("Erreur de requ�te : " + se);
		}
		finally{
			this.dblink.deconnexion();
		}
	}

	public void delete(Formation o) {
		try {
			this.dblink.connexion();
			String r = "DELETE FROM FORMATION WHERE NUMFORMATION="+o.getNumformation();
			
			this.dblink.setPst(this.dblink.getConnection().prepareStatement(r));
			this.dblink.getPst().executeQuery();
		}
		catch(SQLException se) {
			System.out.println("Erreur de requ�te : " + se);
		}
		finally{
			this.dblink.deconnexion();
		}
	}

	@Override
	public Set<Formation> findByContrat(Integer contrat) {
		Set<Formation> Lstretour = new HashSet<Formation>();
		try {
			this.dblink.connexion();
			this.dblink.setPst(this.dblink.getConnection().prepareStatement("SELECT * FROM FORMATION WHERE NUMCONTRAT = "+contrat));
			ResultSet rs = this.dblink.getPst().executeQuery();
			while(rs.next()) {
				Formation retour = new Formation();
				retour.setLibelle(rs.getString("LIBELLE"));
				retour.setNiveau(rs.getString("NIVEAU"));
				retour.setNumformation(rs.getInt("NUMFORMATION"));
				retour.setParcours(rs.getString("PARCOURS"));
				retour.setTypeformation(rs.getString("TYPEFORMATION"));
				retour.setContratquadriennal(new DAOContratquadriennalImpl().find(rs.getInt("NUMCONTRAT")));
				
				Lstretour.add(retour);
			}		
		}
		catch(SQLException se) {
			se.printStackTrace();
		}
		finally{
			this.dblink.deconnexion();
		}
		return Lstretour;
	}

}
